package JDBC_Test.Fruit.Dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import JDBC_Test.Fruit.JDBCUtil;
import JDBC_Test.Fruit.Model.FruitInMedel;
import JDBC_Test.Fruit.Model.FruitModel;
import JDBC_Test.Fruit.Model.SaleRecordModel;

public class FruitDao {
	Connection con = null;
	Statement st = null;
	PreparedStatement ps = null;

	/**
	 * 更新水果库存库存
	 * 
	 * @param inMedel
	 * @throws SQLException
	 */
	public void updateStock(FruitInMedel inMedel) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "update fruit set stock = stock + ? where fruit_name = ?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, inMedel.getAmount());
			ps.setString(2, inMedel.getFruitName());
			ps.executeUpdate();

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}
	}



	/**
	 * 进货时，插入
	 * 
	 * @param fruitModel
	 * @throws SQLException
	 */
	public void insert(FruitModel fruitModel) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "insert into fruit (fruit_name,in_price,out_price,stock) values(?,?,?,?)";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, fruitModel.getFruitName());
			ps.setBigDecimal(2, fruitModel.getInPrice());
			ps.setBigDecimal(3, fruitModel.getOutPrice());
			ps.setInt(4, fruitModel.getStock());
			ps.executeUpdate();
//			System.out.println("插入了");
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

	}

	/**
	 * 查询某水果是否存在水果表中
	 * 
	 * @param fruitModel
	 * @return
	 * @throws SQLException
	 */
	public int select(FruitModel fruitModel) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select * from fruit where fruit_name = ?";
		int count = 0;
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, fruitModel.getFruitName());
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				count++;
			}
			return count;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

		return count;
	}
	
	/**
	 * 查某种水果的库存
	 * 
	 * @param f1
	 * @return
	 * @throws SQLException
	 */
	public int selectStock(FruitModel f1) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select stock from fruit where fruit_name = ?";
		int count = 0;
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, f1.getFruitName());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt("stock");
			}
			return count;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);

		}
		return count;
	}

	/**
	 * 查出某种水果的进价
	 * 
	 * @param f1
	 * @return
	 * @throws SQLException
	 */
	public BigDecimal selectInPrice(FruitModel f1) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select in_price from fruit where fruit_name = ?";
		BigDecimal b1 = null;
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, f1.getFruitName());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				b1 = rs.getBigDecimal("in_price");
			}
			return b1;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}
		return b1;
	}

	/**
	 * 查某种水果的售价
	 * 
	 * @param f1
	 * @return
	 * @throws SQLException
	 */
	public BigDecimal selectOutPrice(FruitModel f1) throws SQLException {
		String sql = "select out_price from fruit where fruit_name = ?";
		BigDecimal b1 = null;
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, f1.getFruitName());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				b1 = rs.getBigDecimal("out_price");
			}
			return b1;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}
		return b1;
	}
	/**
	 * 动态查询，可以代替上面的4个查询
	 * @param f2
	 * @return
	 * @throws SQLException
	 */
	public FruitModel selectDynamic(FruitModel f2) throws SQLException {
		StringBuffer sql = new StringBuffer("select ");
		sql.append("fruit_name,in_price,out_price,stock ");
		sql.append("from fruit where 1=1 ");
		//动态拼接sql，和动态获取查询条件
		List<Object> list = appendWhere(sql,f2);
		FruitModel fruitModel = new FruitModel(null,null,null,0);
		ResultSet rs = null;
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql.toString());
			//把条件拼到sql中
//			System.out.println(list.size());
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			rs = ps.executeQuery();
			while(rs.next()) {
				fruitModel.setFruitName(rs.getString("fruit_name"));
				fruitModel.setInPrice(rs.getBigDecimal("in_price"));
				fruitModel.setOutPrice(rs.getBigDecimal("out_price"));
				fruitModel.setStock(rs.getInt("stock"));
//				System.out.println(rs.getInt("stock"));
			}
//			System.out.println("结果级空");
			return fruitModel;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con, st, ps);
		}
		return fruitModel;
	}
	/**
	 * 动态拼接sql，和动态获取查询条件
	 * @param sql
	 * @param f2
	 * @return
	 */
	private List<Object> appendWhere(StringBuffer sql, FruitModel f2) {
		// TODO Auto-generated method stub
		List<Object> list = new ArrayList<Object>();
		String fruitName = f2.getFruitName();
		if(fruitName!=null) {
			sql.append(" and fruit_name = ?");
			list.add(fruitName);
		}
		BigDecimal inprice = f2.getInPrice();
		if(inprice!=null) {
			sql.append(" and in_price = ?");
			list.add(inprice);
		}
		BigDecimal outprice = f2.getOutPrice();
		if(outprice!=null) {
			sql.append(" and out_price =?");
			list.add(outprice);
		}
		Integer stock = f2.getStock();
		if(stock!=null&&stock!=0) {
			sql.append(" and stock = ?");
			list.add(stock);
		}
		return list;
	}



	/**
	 * 购买时，扣减库存
	 * 
	 * @param weight
	 * @param f2
	 * @throws SQLException
	 */
	public void decreaseStock(int weight, FruitModel f2) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "update fruit set stock = stock - ? where fruit_name = ?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, weight);
			ps.setString(2, f2.getFruitName());
			ps.executeUpdate();

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

	}

	/**
	 * 显示库存
	 * 
	 * @throws SQLException
	 */
	public void showAll() throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select fruit_name,stock from fruit";
		try {
			con = JDBCUtil.getConnection();
			st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				String name = rs.getString("fruit_name");
				int stock = rs.getInt("stock");
				System.out.println("水果： " + name + " 库存：" + stock);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

	}

	

}
